#! /usr/bin/env python3
# -*- coding: utf-8 -*-
#######################
#	获取新浪网站企业财报信息
#######################

import sys
import time
import urllib
import re
from bs4 import BeautifulSoup
import io
import os
dirs = os.path.abspath(os.path.dirname(__file__)+"/../Config")
os.sys.path.append(dirs)   #将上上级目录加载到python的环境变量中
# os.sys.path.append("D:/job/crawler/Config")
from config import conn,driver,logpath
import random

print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),'Began to crawl data...')

cur = conn.cursor()
currentyear = time.strftime("%Y", time.localtime())
years = [int(currentyear)-1,int(currentyear)-2,int(currentyear)-3]
#years = []

print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"open Browser...")
#driver = webdriver.Firefox()
#driver = webdriver.PhantomJS(executable_path='D:/phantomjs198/phantomjs.exe')
#driver = webdriver.PhantomJS(executable_path='D:/phantomjs198/phantomjs.exe')
#driver = webdriver.PhantomJS(executable_path='/usr/local/phantomjs')
#driver = connect(webdriver.DesiredCapabilities.HTMLUNIT, server="http://localhost:4444")
#driver = webdriver.Remote(desired_capabilities=webdriver.DesiredCapabilities.HTMLUNIT)
#driver = webdriver.Remote("http://localhost:4444/wd/hub", webdriver.DesiredCapabilities.HTMLUNIT.copy())
#driver = webdriver.Remote(desired_capabilities=DesiredCapabilities.HTMLUNIT)     #（2）
time.sleep(5)

#获取远程URL链接
def GetUrlData(url):
	data=urllib.request.urlopen(url).read()
	return data

#递归获取页面内容 如果页面载入失败 重新加载
def DriverGet(url,tableid,eid,enterprise,times=1):
	js='window.open("%s")' % url
	driver.execute_script(js)
	handles = driver.window_handles # 获取当前窗口句柄集合（列表类型）
	driver.switch_to_window(handles[-1])
	# driver.get(url)
	driver.implicitly_wait(10)
	time.sleep(10)
	soup = None
	soup = BeautifulSoup(driver.page_source,"html.parser")
	if(soup.find("table",id=tableid) or soup.find("div",id=tableid)):
		return soup
	else:
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Reload the page failed to open the page ",times," time...")		
		if(times < 5):
			times = times+1
			return DriverGet(url,tableid,eid,enterprise,times)
		else:
			print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"current enterprise Information fetching failure...")
			#保存抓取失败企业
			insertsql = "insert into crawlerfalselog (eid,ename,url,type) values (%s,'%s','%s',1)" % (eid,enterprise,url)
			try:
				cur.execute(insertsql)
				conn.commit()
			except Exception as e:
				print("Data add failure")
			driver.close()
			driver.quit()
			exit()

#获取详请页面内容 处理
def dealData(searchname):
	print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"get sina company code...")
	url = "http://suggest3.sinajs.cn/suggest/type=&key=%s" % urllib.request.quote(searchname)
	geturlData = GetUrlData(url).decode("gbk")
	enterprisecode = geturlData.split("=")[1].split(",")[2]
	enterprisestockcode = geturlData.split("=")[1].split(",")[3]

	eid = random.randint(10000000,99999999)
	
	#查询公司在本行业内的排名
	print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Open the company homepage")
	soup = DriverGet("http://finance.sina.com.cn/realstock/company/%s/nc.shtml" % enterprisestockcode,"rankSummary_l",eid=eid,enterprise=searchname)
	fullname = soup.find("div",class_="com_overview").find_all("p")[1].text.strip()
	try:
		sy = soup.find("div",id="rankSummary_l").find_all("span")
		sylist = sy[2].text.strip()
		pattern = re.compile("第(\d*)\((\d*)家\)")
		res = pattern.search(sylist).groups()
		rankSummary = "%s/%s" % (res[0],res[1])
	except Exception as e:
		rankSummary = "无"
	##########################

	for year in years:
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"get ",year," year data")

		##########################################################财务摘要
		"""
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"打开 【",searchname,"】 公司财务摘要页面")
		driver.get("http://vip.stock.finance.sina.com.cn/corp/go.php/vFD_FinanceSummary/stockid/%s/displaytype/4.phtml" % enterprisecode)
		driver.implicitly_wait(10)
		time.sleep(5)
		soup = BeautifulSoup(driver.page_source,"html.parser")
		cwzy_trlist = soup.find("table",id="FundHoldSharesTable").find_all("tr")
		#去掉表头
		cwzy={}
		for cwzy_tr in cwzy_trlist[1:]:
			cwzy_tdlist = cwzy_tr.find_all("td")
			if(len(cwzy_tdlist)>=2):
				key = cwzy_tdlist[0]
				value = cwzy_tdlist[1]
				cwzy[key.text.strip()] = value.text.strip()
		"""

		#############################################################财务指标
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Open the company financial index page")
		soup = DriverGet("http://money.finance.sina.com.cn/corp/go.php/vFD_FinancialGuideLine/stockid/%s/ctrl/%s/displaytype/4.phtml" % (enterprisecode,year),"BalanceSheetNewTable0",eid=eid,enterprise=searchname)
		cwzb_trlist = soup.find("table",id="BalanceSheetNewTable0").find_all("tr")
		#去掉表头
		cwzb={}
		for cwzb_tr in cwzb_trlist[1:]:
			cwzb_tdlist = cwzb_tr.find_all("td")
			if(len(cwzb_tdlist)>=2):
				key = cwzb_tdlist[0]
				value = cwzb_tdlist[1]
				cwzb[key.text.strip()] = value.text.strip().replace(',','').replace('--','0')
		#print(cwzb)
		#print(driver.window_handles)

		#############################################################资产负债表
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Open the company's balance sheet page")
		soup = DriverGet("http://money.finance.sina.com.cn/corp/go.php/vFD_BalanceSheet/stockid/%s/ctrl/%s/displaytype/4.phtml" % (enterprisecode,year),"BalanceSheetNewTable0",eid=eid,enterprise=searchname)
		zcfz_trlist = soup.find("table",id="BalanceSheetNewTable0").find_all("tr")
		#去掉表头
		zcfz={}
		for zcfz_tr in zcfz_trlist[1:]:
			zcfz_tdlist = zcfz_tr.find_all("td")
			if(len(zcfz_tdlist)>=2):
				key = zcfz_tdlist[0]
				value = zcfz_tdlist[1]
				zcfz[key.text.strip()] = value.text.strip().replace(',','').replace('--','0')
		#print(zcfz)
		#print(driver.window_handles)

		#############################################################利润表
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Open the company profit page")
		soup = DriverGet("http://money.finance.sina.com.cn/corp/go.php/vFD_ProfitStatement/stockid/%s/ctrl/%s/displaytype/4.phtml" % (enterprisecode,year),"ProfitStatementNewTable0",eid=eid,enterprise=searchname)
		lr_trlist = soup.find("table",id="ProfitStatementNewTable0").find_all("tr")
		#去掉表头
		lr={}
		for lr_tr in lr_trlist[1:]:
			lr_tdlist = lr_tr.find_all("td")
			if(len(lr_tdlist)>=2):
				key = lr_tdlist[0]
				value = lr_tdlist[1]
				lr[key.text.strip()] = value.text.strip().replace(',','').replace('--','0')
		#print(lr)
		#print(driver.window_handles)

		#############################################################现金流量表
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Open the company's cash flow statement page")
		soup = DriverGet("http://money.finance.sina.com.cn/corp/go.php/vFD_CashFlow/stockid/%s/ctrl/%s/displaytype/4.phtml" % (enterprisecode,year),"ProfitStatementNewTable0",eid=eid,enterprise=searchname)
		xjll_trlist = soup.find("table",id="ProfitStatementNewTable0").find_all("tr")
		#去掉表头
		xjll={}
		for xjll_tr in xjll_trlist[1:]:
			xjll_tdlist = xjll_tr.find_all("td")
			if(len(xjll_tdlist)>=2):
				key = xjll_tdlist[0]
				value = xjll_tdlist[1]
				xjll[key.text.strip()] = value.text.strip().replace(',','').replace('--','0')
		#print(xjll)
		#print(driver.window_handles)

		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Data mining...")

		dqgxzw = float(zcfz["短期借款"])+float(zcfz["交易性金融负债"])+float(zcfz["应付票据"])+float(zcfz["一年内到期的非流动负债"])+float(zcfz["应付短期债券"])
		if(dqgxzw == 0):
			kzyzpxjldqgxzwb = 0
		else:
			kzyzpxjldqgxzwb = format((float(xjll["经营活动产生的现金流量净额"])-float(xjll["购建固定资产、无形资产和其他长期资产所支付的现金"]))/dqgxzw,'.2f')
		if(lr["利息支出"] == "0"):
			kzyzpxjlllxbzbs = 0
			elxbzbs = 0
		else:
			kzyzpxjlllxbzbs = format((float(xjll["经营活动产生的现金流量净额"])-float(xjll["购建固定资产、无形资产和其他长期资产所支付的现金"]))/float(lr["利息支出"]),'.2f')
			elxbzbs = format(float(lr["三、营业利润"])/float(lr["利息支出"]), '.2f')
		ybxzchj = format(float(zcfz["流动资产合计"])/float(zcfz["资产总计"])*100, '.2f')
		#kzyzpxjll = format(float(xjll["经营活动产生的现金流量净额"])+float(xjll["投资活动产生的现金流量净额"]), '.2f')
		kzyzpxjll = format(float(xjll["经营活动产生的现金流量净额"])-float(xjll["购建固定资产、无形资产和其他长期资产所支付的现金"]), '.2f')
		#添加 行业排名 cwzb["存货周转率(次)"] xjll['经营活动现金流入小计'] cwzb['净资产收益率']
		gxzw = format(dqgxzw + float(zcfz["长期借款"]) + float(zcfz["应付债券"]), '.2f')
			
		sql = "replace into enterprisefinance (`eid`,`ename`,`fullname`,`year`,`gdzc`,`jyxjrzc`,`hbzj`,`qtzc`,`ybxzchj`,`zzc`,`dqjk`,`yfpj`,`ynndqdfldfz`,`ldfzhj`,`cqjk`,`fldfzhj`,`fzhj`,`sszb`,`syzjchj`,`syl`,`yysr`,`qjfy`,`jlr`,`jyhdcsdxjllje`,`gjcqzjszfdxj`,`czcqzjszfdxj`,`kzyzpxjll`,`czhdjxjl`,`EBIT`,`dqgxzw`,`gxzw`,`mll`,`zcfzl`,`kzyzpxjlllxbzbs`,`kzyzpxjldqgxzwb`,`elxbzbs`,`xjhll`,`dbbl`,`chzzl`,`jyhdxjlrxj`,`jzcsyl`,`hypm`) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (eid,searchname,fullname,year,zcfz["非流动资产合计"],zcfz["交易性金融资产"],zcfz["货币资金"],"",ybxzchj,zcfz["资产总计"],zcfz["短期借款"],zcfz["应付票据"],zcfz["一年内到期的非流动负债"],zcfz["流动负债合计"],zcfz["长期借款"],zcfz["非流动负债合计"],zcfz["负债合计"],zcfz["实收资本(或股本)"],zcfz["所有者权益(或股东权益)合计"],"",lr["营业收入"],lr["二、营业总成本"],lr["四、净利润"],xjll["经营活动产生的现金流量净额"],xjll["购建固定资产、无形资产和其他长期资产所支付的现金"],xjll["处置固定资产、无形资产和其他长期资产所收回的现金净额"],kzyzpxjll,xjll["筹资活动产生的现金流量净额"],lr["利润总额"],format(dqgxzw, '.2f'),gxzw,format(float(cwzb["营业利润率(%)"]), '.2f'),format(float(cwzb["资产负债率(%)"]), '.2f'),kzyzpxjlllxbzbs,kzyzpxjldqgxzwb,elxbzbs,"","",format(float(cwzb["存货周转率(次)"]), '.2f'),xjll['经营活动现金流入小计'],format(float(cwzb['净资产收益率(%)']), '.2f'),rankSummary)

		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Corporate earnings data warehousing...")
		exestatus = "[SUCCESS]:"
		try:
			cur.execute(sql)
			conn.commit()
		except Exception as e:
			print("Data add failure ")
			exestatus = "[ERROR]:"

		#file_object = open(logpath+"/finance.log", 'a')
		#file_object.write(exestatus+"	"+time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())+"	"+sql+"\r\n")
		#file_object.close()
		#########数据处理完

print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Start to get the company data...")
#企业列表
if "php" in sys.argv:
	enterprise = urllib.request.unquote(sys.argv[1])
else:
	enterprise = sys.argv[1]
dealData(enterprise)
driver.close()
driver.quit()
